<?php

namespace ShowStop\AdminBundle\Controller;

use Doctrine\DBAL\Schema\ForeignKeyConstraint;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Symfony\Component\HttpFoundation\Request;
use \FacebookApiException;

use ShowStop\TelevisionBundle\Entity\Series;

/**
* @Route("/admin")
*/
class DefaultController extends Controller
{
	/**
	* @Route("/notify", name="_notify")
	* @Template()
	*/
	public function notificationsAction()
	{
		//get facebook api
		$facebook = $this->get('fos_facebook.api');
		
		//load list of unprocessed notifications
		$user_id[] = '100003178977968';
		$user_id[] = '100003202599751';
		
		
		foreach( $user_id as $user ) {
			//create message to send to facebook user feed
			$args = array(
			            'message'   => 'Test: Dexter returns Oct 01, 2011',
			            'link'      => 'http://showstop.tv/series/886',
			            'caption'   => 'Test: Dexter returns Oct 01, 2011',
			);
			
			//post it to their feed
			$post_id = $facebook->api("/".$user."/feed", "post", $args);
		}
		
		//return response		
		return $this->render('ShowStopAdminBundle:Default:index.html.twig', array( ) );
	}
	
	private function getTvDbId( $name ) {
		$responseUrl = 'http://thetvdb.com/api/GetSeries.php?seriesname=%s&language=%l';
		$language = "en";
		try {
			$xml = simplexml_load_file( sprintf( $responseUrl, urlencode( $name ), $language ) );
		} catch( ErrorException $e ) {
			return -1;
		}
		
		return $xml->Series[0] != null ? $xml->Series[0]->seriesid : -1;
	}
	
	private function getTvDbSeries( $id ) {
		$api_key = 'C39165096A3B58D7';
		$responseUrl = 'http://thetvdb.com/api/%s/series/%d';
		$xmlFile = sprintf( $responseUrl, $api_key, $id );
		try {
			$xml = simplexml_load_file( $xmlFile );
		} catch( ErrorException $e ) {
			return array();
		}
		
		return $xml->Series[0] != null ? $xml->Series[0] : array();
	}
	
	/**
	* @Route("/", name="_admin")
	* @Template()
	*/
	public function indexAction()
	{
		$form = $this->createFormBuilder(array())
		->add('startid', 'text')
		->add('endid','text')
		->add('seriesid','text')
		->getForm();
		 
		return $this->render('ShowStopAdminBundle:Default:index.html.twig', array( 'postCheck' => 0, 'form' => $form->createView() ) );
	}
    /**
     * @Route("/updates/range", name="_updaterange")
     * @Template()
     */
    public function updateAction(Request $request)
    {    	
    	$form = $this->createFormBuilder(array())
    	->add('startid', 'text')
		->add('endid','text')
		->add('seriesid','text')
    	->getForm();
    	
    	if($request->getMethod()=='POST')
    	{
    		$form->bindRequest($request);
    		
    		$data = $form->getData();
    		$series = null;
    		$query = null;
    		//get doctrine entity manager
    		$em = $this->getDoctrine()->getEntityManager();
    		
    		if( $data['seriesid'] != null)
    		{
    			$data['seriesid'];
    			$data['endid'] = $data['seriesid'] +1;
    			
    			$query = $em->createQuery( 'SELECT s FROM ShowStopTelevisionBundle:Series s WHERE s.id >= ' . $data['seriesid'] . ' AND s.id < ' . $data['endid'] );
    		}
    		else
    		{
	    		if($data['endid'] == null)
	    		{
	    			$data['endid'] = $data['startid'] +1;
	    		}
	    		 
	    		$query = $em->createQuery( 'SELECT s FROM ShowStopTelevisionBundle:Series s WHERE s.id >= ' . $data['startid'] . ' AND s.id < ' . $data['endid'] );
    		}
    		
    		if($query != null)
    		{
	    		$series = $query->getResult();
	    		 
	    		if (!$series) {
	    			throw $this->createNotFoundException( "No television show found");
	    		}
	    		 
	    		foreach( $series as $show ) {
	    			try{
	    				if( $show->getTvDbId() == null )
	    				$id = $this->getTvDbId( $show->getName() );
	    				else
	    				$id = $show->getTvDbId();
	    				 
	    				if( $id >= 0 ) {
	    					//lookup show info by tvdb_id
	    					$xmlShow = $this->getTvDbSeries( $id );
	    		
	    					$show->setDescription( (string)$xmlShow->Overview );
	    					$show->setFirstAired( (string)$xmlShow->FirstAired );
	    					$show->setStatus( (string)$xmlShow->Status );
	    					$show->setNetwork( (string)$xmlShow->Network );
	    					$show->setTvdbId( (int)$xmlShow->id );
	    				}
	    			} catch(Exception $e) {
	    				return $this->render('ShowStopAdminBundle:Default:index.html.twig', array( 'postCheck' => 0 )) ;
	    			}
	    		}
	    		$em->flush();
	    		
	    		return $this->render('ShowStopAdminBundle:Default:update_complete.html.twig', array('updatedSeries' => $series, 'createdSeries' => array()));
    		}
	    }
    	//return $this->render('ShowStopAdminBundle:Default:index.html.twig', array() );
    	return $this->render('ShowStopAdminBundle:Default:index.html.twig', array( 'postCheck' => 0, 'form' => $form->createView() )) ;
    }
    
    /**
    * @Route("/updates/new_and_returning")
    * @Template()
    */
    public function returnDatesAction(Request $request)
    {
    	$formats = array();
    	$formats["j/m/Y"] = "j/m/Y";
    	$formats["m/j/Y"] = "m/j/Y";
    	$formats["Y/m/j"] = "Y/m/j";
    	$formats["j-m-Y"] = "j-m-Y";
    	$formats["m-j-Y"] = "m-j-Y";
    	$formats["Y-m-j"] = "Y-m-j";
    	
    	$form = $this->createFormBuilder( array() )
    		->add('records', 'textarea')
    		->add('formats', 'choice', array(
    			'choices' => $formats ) )
    		->getForm();
    	
    	if( $request->getMethod() == "POST" ) {
    		$form->bindRequest($request);
    		
    		$data = $form->getData();
    		$records = explode( "\n", $data["records"] );
    		
    		$format = trim( $data["formats"] );
    		
    		$em = $this->getDoctrine()->getEntityManager();
    		$repository = $em->getRepository('ShowStopTelevisionBundle:Series');
    		
    		
    		$createdSeries = array();
    		$updatedSeries = array();
    		
    		foreach( $records as $record ) {
    			$fields = explode( "|", $record );
    			$showName 	= trim( $fields[0] );
    			$returnDate = trim( $fields[1] );
    			$network 	= trim( $fields[2] );
    			
    			
    			$series = $repository->findOneByName( $showName );
    			
    			$returnDate = date_create_from_format( $format, $returnDate );
				
    			//if exists, update
     			if( $series ) { 
    				$series->setNetwork( $network );
    				if( !$returnDate )
						throw $this->createNotFoundException( "Show= " . $fields[0] . ", Date= ". $fields[1] . ", Network= " . $fields[2] . "Format= " . $format );
    				$series->setReturnDate( $returnDate->format('Y-m-d') );
    				$updatedSeries[] = $series;
    			} 
    			//else create new entry
    			else { 
    				$series = new Series();
    				$series->setName( $showName );
    				$series->setNetwork( $network );
    				if( !$returnDate )
    					throw $this->createNotFoundException( "Show= " . $fields[0] . ", Date= ". $fields[1] . ", Network= " . $fields[2] . "Format= " . $format );
    				$series->setReturnDate( $returnDate->format('Y-m-d') );
    				
    				//lookup other details on the tvdb
    				$id = $this->getTvDbId( $showName );
    				if( $id >= 0 ) {
    					$xmlShow = $this->getTvDbSeries( $id );
    					$series->setDescription( (string)$xmlShow->Overview );
    					$series->setFirstAired( (string)$xmlShow->FirstAired );
    					$series->setStatus( (string)$xmlShow->Status );
    					$series->setNetwork( (string)$xmlShow->Network );
    					$series->setTvdbId( (int)$xmlShow->id );
    				}
    				
    				//set object to be managed
    				$em->persist( $series );
    				$createdSeries[] = $series;
    			}
    			
    		}
    		//commit all the changes to the db
    		$em->flush();
    		return $this->render('ShowStopAdminBundle:Default:update_complete.html.twig', array('updatedSeries' => $updatedSeries, 'createdSeries' => $createdSeries ) );

    	}
    	return $this->render('ShowStopAdminBundle:Default:return_dates.html.twig', array('form' => $form->createView() ) );
    	 
    }
    
    /**
    * @Route("/updates/check/{time}")
    * @Template()
    */
    //time=1318079692
    public function updateCheckAction( $time )
    {
    	$updateList = 'http://thetvdb.com/api/Updates.php?time=' . $time;
    	$xml = simplexml_load_file( $updateList );
    	
    	$em = $this->getDoctrine()->getEntityManager();
    	$repository = $em->getRepository('ShowStopTelevisionBundle:Series');
    	
    	$updatedSeries = array();
    	$createdSeries = array();
    	
    	foreach( $xml->Series as $id  ) {

	    		//lookup show on tvdb with $tvID
	    		unset( $xmlShow );
	    		$xmlShow = $this->getTvDbSeries( $id );
	    		
	    		//lookup show with tvdbid from the repository
	    		$series = $repository->findOneByTvDbID( $id );
	    		
	    		//if exists, update
	    		if( !$series ) {
	    			$series = new Series();
	    			$series->setName( (string)$xmlShow->SeriesName );
	    			$series->setTvDbID( (int)$xmlShow->id );
	    			$series->setDescription( (string)$xmlShow->Overview );
	    			$series->setFirstAired( (string)$xmlShow->FirstAired );
	    			$series->setStatus( (string)$xmlShow->Status );
	    			$series->setNetwork( (string)$xmlShow->Network );
	    			
	    			$createdSeries[] = $series;
	    			
	    			//set to be managed
	    			$em->persist( $series );
	    		} else {
	    			$series->setDescription( (string)$xmlShow->Overview );
	    			$series->setFirstAired( (string)$xmlShow->FirstAired );
	    			$series->setStatus( (string)$xmlShow->Status );
	    			$series->setNetwork( (string)$xmlShow->Network );
	    			
	    			$updatedSeries[] = $series;
	    		}
    		
    	}
    	$em->flush();
    	
		return $this->render('ShowStopAdminBundle:Default:update_complete.html.twig', array('updatedSeries' => $updatedSeries, 'createdSeries' => $createdSeries ) );    
    }
    
    /**
    * @Route("/userstatus/{input}_{range}", name="_userstatus")
    * @Template()
    */
    public function userStatusAction($input = "0", $range = "5" )
    {
    	// Check to see if the input is higher then 0
    	if ( $input < 0)
    	return $this->redirect( $this->generateUrl('_userstatus', array('input' => 0, 'range' => $range)));
    
    	 
    	// Get connection
    	$conn = $this->get('database_connection');
    	 
    	if ( isset($_POST))
    	{
    		// Check to see if the user has changed the number of list to be shown
    		if (isset($_POST['numberShown']))
    		{
    			return $this->redirect( $this->generateUrl('_userstatus',
    			array('input' => $input, 'range' => $_POST['numberShown'])));
    		}
    
    		// Check to see if user status need to be change
    		if (isset($_POST['bannedUser']))
    		{
    			$id = $_POST['bannedUser'];
    			$conn->exec("UPDATE user_role SET role_id = '4' WHERE user_role.user_id = ". $id);
    		}
    		else if (isset($_POST['unBannedUser']))
    		{
    			$id = $_POST['unBannedUser'];
    			$conn->exec("UPDATE user_role SET role_id = '3' WHERE user_role.user_id = ". $id);
    		}
    		else if (isset($_POST['deactivate']))
    		{
    			$id = $_POST['deactivate'];
    			$conn->exec("UPDATE user SET enabled = 'N' WHERE id = ". $id);
    		}
    		else if (isset($_POST['reactivate']))
    		{
    			$id = $_POST['reactivate'];
    			$conn->exec("UPDATE user SET enabled = 'Y' WHERE id = ". $id);
    		}
    		else if (isset($_POST['unadmin']))
    		{
    			$id = $_POST['unadmin'];
    			$conn->exec("UPDATE user_role SET role_id = '1' WHERE user_role.user_id = ". $id);
    		}
    		else if (isset($_POST['admin']))
    		{
    			$id = $_POST['admin'];
    			$conn->exec("UPDATE user_role SET role_id = '2' WHERE user_role.user_id = ". $id);
    		}
    			
    	}
    	 
    	// Check to see if it search, otherwise get everyone info
    	if (isset($_POST['searchTextBox']))
    	{
    		$name = mysql_real_escape_string($_POST['searchTextBox']);
    		$results = $conn->fetchAll( "SELECT role_id, email, firstname, lastname, username, id, enabled FROM user AS e
    						INNER JOIN user_role AS p ON e.id = p.user_id
    						WHERE username LIKE '%" .$name ."%'");
    	}
    	else
    	{
    		$results = $conn->fetchAll( "SELECT role_id, email, firstname, lastname, username, id, enabled FROM user AS e
        					INNER JOIN user_role AS p ON e.id = p.user_id 
        					LIMIT " .$input ."," .$range);
    	}
    
    	// Get number of all the  users
    	$numOfAllRows = $conn->fetchAll( "SELECT COUNT(*) AS number FROM user");
    	 
    	$next = intval($input) + intval($range);
    	$previous = (intval($input) - intval($range)) <= 0 ? 0 : intval($input) - intval($range);
    
    	// array that saves all the ranges needed in the page
    	$ranges = array( $next , $previous , intval($range), $numOfAllRows, $input );
    	 
    	// Array that saves all the numbered needed
    	$listOfnumber	= array();
    	for( $i = 5 ; $i <= 100 ; $listOfnumber[] = $i, $i += 5 );
    	 
    	 
    	return $this->render('ShowStopAdminBundle:Default:user_status.html.twig', array( 'listOfNumber' =>  $listOfnumber, 'users' => $results, 'next' => $ranges));
    }
    
    /**
    * @Route("/userEdit", name="_userEdit")
    * @Template()
    */
    public function userEditsAction( )
    {
    	// Get connection
    	$conn = $this->get('database_connection');
    	 
    	if (isset($_POST['accept']))
    	{
    		$conn->exec("UPDATE user_edited_series SET submission_status = 'accepted' WHERE id = ". $_POST['accept']);
    		$info = $conn->fetchAll( "SELECT * FROM user_edited_series WHERE id = ". $_POST['accept']);
    
    		$sql = "UPDATE series SET  first_aired = '" . mysql_real_escape_string($info[0]['first_aired']) . "'"
    		.", network = '" . mysql_real_escape_string($info[0]['network']) . "'"
    		.", status = '" . mysql_real_escape_string($info[0]['status']) . "'"
    		.", return_date = '" . mysql_real_escape_string($info[0]['return_date']) . "'"
    		.", first_aired = '" . mysql_real_escape_string($info[0]['first_aired']) . "'"
    		.", description = '" . mysql_real_escape_string($info[0]['description']) . "'"
    		. "WHERE id = ". $info[0]['series_ID'];
    		$conn->exec($sql);
    
    	}
    
    	if (isset($_POST['decline']))
    	{
    		$conn->exec("UPDATE user_edited_series SET submission_status = 'declined' WHERE id = ". $_POST['decline']);
    	}
    	 
    	$row = $conn->fetchAll( "SELECT * FROM user_edited_series WHERE submission_status = 'pending'");
    	 

    	$currentlySelected = -1;
    	 
    	$series = -1;
    	$userSumbittion = null;
    	$userStatus = null;
    	if (isset($_POST['id']))
    	{
    		$currentlySelected = $_POST['id'];
    
    		$userSumbittion = $conn->fetchAll( "SELECT * FROM user_edited_series where id = " .$currentlySelected  );
    
    		$series = $conn->fetchAll( "SELECT * from series where id = " . $userSumbittion[0]['series_ID'] );
    		 
    		$sql = "SELECT count( * ) AS accepted FROM user
    					INNER JOIN user_edited_series ON user.id = user_edited_series.user_id
    					WHERE user.id = ". $row[0]['user_id'] ." AND user_edited_series.submission_status = 'accepted'";
    
    		$tempUser = $conn->fetchAll( $sql );
    
    		$userStatus['accepted'] =  $tempUser[0]['accepted'];
    
    		$sql = "SELECT count( * ) AS pending FROM user
    	    						INNER JOIN user_edited_series ON user.id = user_edited_series.user_id
    	    						WHERE user.id = ". $row[0]['user_id'] ." AND user_edited_series.submission_status = 'pending'";
    
    		$tempUser = $conn->fetchAll( $sql );
    
    		$userStatus['pending'] =  $tempUser[0]['pending'];
    
    
    		$sql = "SELECT count( * ) AS declined , user.username FROM user
    	    			INNER JOIN user_edited_series ON user.id = user_edited_series.user_id
    	    			WHERE user.id = ". $row[0]['user_id'] ." AND user_edited_series.submission_status = 'declined'";
    
    		$tempUser = $conn->fetchAll( $sql );
    
    		$userStatus['declined'] =  $tempUser[0]['declined'];
    
    		$userStatus['userName'] =  $tempUser[0]['username'];
    
    
    		$userStatus['total'] = $userStatus['accepted'] + $userStatus['pending'] + $userStatus['declined'] ;
    	}
    	 
    	return $this->render('ShowStopAdminBundle:Default:user_edit.html.twig',
    	array( 'userStatus' => $userStatus , 'row' => $row, 'userSumbittion' => $userSumbittion, 'series' => $series, 'selected' => $currentlySelected ));
    }
}
